package com.green.web.excel;

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.JSONSerializer;
import net.sf.json.JsonConfig;
import net.sf.json.processors.JsonValueProcessor;
import net.sf.json.util.PropertyFilter;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;

import com.green.common.util.StringUtil;
import com.green.common.util.utils.StringUtils;
import com.green.common.util.utils.date.DateUtils;
import com.green.service.common.ExportExcelService;
import com.green.web.controller.common.BaseController;
/**
 * excel控制类
 * @author:guhui
 * @date:2012-9-9下午06:29:45
 *
 */
@Controller
public class ExcelController extends BaseController {
	@Autowired
	private ExportExcelService exportExcelService;
	
	private HSSFWorkbook workbook =null;

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public void excelExprot(String key,Map<String,Object> params,HttpServletResponse response) throws Exception{
		Map<String,Object> excelMap=ExcelUtils.getConfigById(key);
		String fileName="";
		 workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("sheet");
		if(excelMap!=null){
			List<Map<String,Object>> column=(List<Map<String,Object>>) excelMap.get("columns");			
			String sqlMapsId=(String) excelMap.get("sqlMapsId");
			fileName=(String) excelMap.get("fileName");
			List list = getData4FillSheet(sqlMapsId, params);
			JSONArray array=beanToJSON(list,column);
			createHeader(sheet, column);			
			createBook(sheet, array,column);
		}		
		exportExcel(workbook, response, fileName);
	}

	/**
	 * 
	 * intro: query data from db meanwhile allow(recommend) to be overwrite the
	 * method
	 * 
	 * @author Lineshow
	 * @since 2012-8-17
	 * @param sqlCoordinate
	 * @param params
	 *            parameters
	 */
	@SuppressWarnings("rawtypes")
	public List getData4FillSheet(String sqlCoordinate,
			Map<String, Object> params) {
		return this.exportExcelService
				.getListBySqlMapsId(sqlCoordinate, params);
	}

	/**
	 * 转换成JSONARRAY
	 * @param list
	 * @param clazz
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public JSONArray beanToJSON(List list,final List<Map<String,Object>> column){

		Map<String,Object> maps=new HashMap<String, Object>();
		for(int i=0;column!=null && i<column.size();i++){
			Map<String,Object> obj=column.get(i);
			maps.putAll(obj);
		}
		JSONArray array = (JSONArray) JSONSerializer.toJSON(list,
				fillJsonConfig(maps));
		return array;
	}
	
	public JSONArray beanToJSON2(List<T> list,final List<Map<String,Object>> column){
		JsonConfig config=new JsonConfig();
		Map<String,Object> maps=new HashMap<String, Object>();
		for(int i=0;column!=null && i<column.size();i++){
			Map<String,Object> obj=column.get(i);
			maps.putAll(obj);
		}
		final Map<String,Object> map=maps;
		config.setJsonPropertyFilter(new PropertyFilter() {
			@Override
			public boolean apply(Object arg0, String arg1, Object arg2) {
				
				if(map.get(arg1.toUpperCase())!=null){
					return false;
				}else{
					return true;
				}
			}
		});
		JSONArray array=(JSONArray) JSONSerializer.toJSON(list,config);
		return array;
	}

	/**
	 * 
	 * intro:register related json-config.
	 * 
	 * @author Lineshow
	 * @since 2012-8-17
	 */
	private JsonConfig fillJsonConfig(final Map<String, Object> map) {
		JsonConfig config = new JsonConfig();
		config.setJsonPropertyFilter(new PropertyFilter() {
			@Override
			public boolean apply(Object arg0, String arg1, Object arg2) {
				if(map.get(arg1.toUpperCase())!=null){
					return false;
				}else{
					return true;
				}
			}
		});
		config.registerJsonValueProcessor(java.sql.Timestamp.class,
				new JsonValueProcessor() {

			@Override
			public Object processArrayValue(Object arg0, JsonConfig arg1) {
						return null;
			}
			@Override
			public Object processObjectValue(String arg0, Object arg1,
					JsonConfig arg2) {
					return DateUtils.toString((Date) arg1,
							DateUtils.YEAR_MONTH_DAY);
			}
		});
		config.registerJsonValueProcessor(java.sql.Date.class,
				new JsonValueProcessor() {

					@Override
					public Object processArrayValue(Object arg0, JsonConfig arg1) {
						return null;
					}
					@Override
					public Object processObjectValue(String arg0, Object arg1,
							JsonConfig arg2) {
						return DateUtils.toString((Date) arg1,
								DateUtils.YEAR_MONTH_DAY);
					}
				});
		return config;
	}
	
	/**
	 * 构建表头
	 * @param sheet
	 * @param column
	 */
	@SuppressWarnings({ "rawtypes", "unchecked", "deprecation" })
	public void createHeader(HSSFSheet sheet,List<Map<String,Object>> list){
		HSSFRow row = sheet.createRow(0);
		
		short index=0;
		for(Iterator iter=list.iterator();iter.hasNext();){
			Map<String,Object> column=(Map<String, Object>) iter.next();
			String key=(String) column.keySet().toArray()[0];
			
			for(int kk=0;kk<column.keySet().toArray().length;kk++){
				String str=(String) column.keySet().toArray()[kk];
				if(!str.equals("defaultKey") && !str.equals("defaultDescription") &&!str.equals("width") && !str.equals("enum")
						&& !str.equals("dataType")){
					key=str;
					break;
				}
			}
			HSSFCell cell=row.createCell(index);
			cell.setCellValue((String)column.get(key));
			
			sheet.setColumnWidth(index, column.get("width")==null?200:new BigDecimal((String)column.get("width")).intValue()*36);
			//column.remove("width");
			if(index>=list.size()&& key!=null && key.equals("width")){
				key=(String) column.keySet().toArray()[1];
			}
			index++;
		}
	}
	/**
	 * 构建数据表格
	 * @param sheet
	 * @param list
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void createBook(HSSFSheet sheet,JSONArray array,List<Map<String,Object>> list){
		for(int i=0; array!=null && i<array.size();i++){
			JSONObject object=array.getJSONObject(i);
			
			HSSFRow row=sheet.createRow(i+1);
			int index=0;
			Class clazz=null;
			for(Iterator iter=list.iterator();iter.hasNext();){
				Map<String,Object> column=(Map<String, Object>) iter.next();
				String key=getKey(column);
				HSSFCell cell=row.createCell(index++);
				try {
					if(column.containsKey("enum")){
						String enumStr=(String) column.get("enum");
						String defaultKey=(String) column.get("defaultKey");
						String defaultDescription=(String)column.get("defaultDescription");
						if(key!=null && (key.equals(defaultKey)|| key.equals(defaultDescription))){
							
						}
						for(int kk=0;kk<column.keySet().toArray().length;kk++){
							String str=(String) column.keySet().toArray()[kk];
							if(!str.equals("defaultKey") && !str.equals("defaultDescription") &&!str.equals("width") && !str.equals("enum")){
								key=str;
								break;
							}
						}
						clazz=Class.forName(enumStr);
						
						cell.setCellValue(this.getEnumValues(clazz,this.getStringJsonValueIgnoreCase(object, key),defaultKey,defaultDescription));
					}else if(column.containsKey("dataType")){
						String dataType=(String) column.get("dataType");
						if(dataType.equals("number")){
							HSSFCellStyle cellStyle = this.workbook.createCellStyle();
							cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
				            cell.setCellStyle(cellStyle);
						}
						String objectStr=this.getStringJsonValueIgnoreCase(object, key);
						if(objectStr==null || objectStr.equals("") || objectStr.equals("{}")){
							objectStr="";
						}
						cell.setCellValue(Double.valueOf(objectStr));
					}else{
						String objectStr=this.getStringJsonValueIgnoreCase(object, key);
						if(objectStr==null || objectStr.equals("") || objectStr.equals("{}")){
							objectStr="";
						}
						cell.setCellValue(objectStr);
					}	
				} catch (Exception e) {
					// TODO Auto-generated catch block
					cell.setCellValue("");
				} 	
			}			
		}
	}
	
	private String getKey(Map<String,Object> column){
		String key="";
		for(int kk=0;kk<column.keySet().toArray().length;kk++){
			String str=(String) column.keySet().toArray()[kk];
			if(!str.equals("defaultKey") && !str.equals("defaultDescription") &&!str.equals("width") && !str.equals("enum")
					&& !str.equals("dataType")){
				key=str;
				break;
			}
		}
		return key;
	}
	/**
	 * 构建行
	 * @param row
	 * @param keyList
	 */
	@SuppressWarnings("unused")
	public void createRow(HSSFRow row,List<String> keyList,JSONObject object){
		for(int i=0;keyList!=null && i<keyList.size();i++){
			HSSFCell cell=row.createCell(i);
		}
	}
	/**
	 * 导出
	 * @param workbook
	 * @param response
	 * @param fileName
	 * @throws Exception
	 */
	public void exportExcel(Workbook workbook,HttpServletResponse response,String fileName) throws Exception{
		if(fileName==null || fileName.equals("")){
			response.setHeader("Content-disposition", "attachment; filename = "+ "电子表格.xls"+"");
		}else{
			fileName=fileName+".xls";
			response.setHeader("Content-disposition", "attachment; filename = "+ new String(fileName.getBytes("GB2312"),"ISO8859_1")+"");
		}
		OutputStream stream=response.getOutputStream();
		workbook.write(stream);
		stream.close();
	}
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private String getEnumValues(Class<? extends Enum> c, String key,
			String defaultKey, String defaultDescription) {
		  String description="";
	            try {
			String expressMethodName = "get"
					+ StringUtils.capitalize(defaultDescription);
			if ("enumKey".equals(defaultKey)) {// 默认取枚举名
					Enum enumObj = Enum.valueOf(c, key);
				description = (String) c.getMethod(expressMethodName)
							.invoke(enumObj);
					return description;
				}
			Method m = c.getDeclaredMethod("values");
			Object[] result = (Object[]) m.invoke(c);
					List list = Arrays.asList(result);
			Iterator it = list.iterator();
				while (it.hasNext()) {
					Object objOne = it.next();
					Field code = objOne.getClass().getDeclaredField(defaultKey);
					Field codeDesc = objOne.getClass().getDeclaredField(
							defaultDescription);
					code.setAccessible(true);
					codeDesc.setAccessible(true);
					if (key != null && key.equals(code.get(objOne))) {
						description = (String) codeDesc.get(objOne);
						break;
					}
				}

	            } catch (Exception e) {
	                e.printStackTrace();
	            }
	        return description;
	    }
	  
	  public static void main(String[] args) {
	        
	    }
	  
	  /**
	   * 取的object 对应的key的值 不区分大小写 解决 object toJson 大小写的问题
	   * @param object
	   * @param key
	   * @return
	   */
	@SuppressWarnings("unchecked")
	private Object getJsonValueIgnoreCase(JSONObject object,String key){
		if(object.containsKey(key)){
			return object.get(key);
		}
		final Set<String> keys=object.keySet();
		  for (String item : keys) {
			if(key.equalsIgnoreCase(item)){
				return object.get(item);
			}
		}
		return null;
	  }
	
	private String getStringJsonValueIgnoreCase(JSONObject object,String key){
		return StringUtil.valueOf(this.getJsonValueIgnoreCase(object, key));
	  }
}
